﻿import pandas as pd
from sqlalchemy import create_engine

# 创建SQL server 数据库连接
engine = create_engine(
    "mssql+pyodbc://pereader:pereader@172.18.65.31,1433/SortingDB?driver=ODBC+Driver+17+for+SQL+Server")
# 显示所有的列 方便调试
pd.set_option('display.max_columns', None)


def get_flag(StandardPartID):
    df = pd.read_sql(f"SELECT 分光,分板 FROM [dbo].[MO_SCHEDULE_10min] WHERE [实际标准件]='{StandardPartID}'", engine)
    for i in list(set(df['分光'])):
        if i:
            return False
    for i in list(set(df['分板'])):
        if i:
            return False
    return True


def get_days(StandardPartID):
    df = pd.read_sql(f"SELECT DATEDIFF(DD,MIN(TestTime),GETDATE()) AS 天数 FROM [dbo].[StandardPartData] "
                     f"WHERE StandardPartID='{StandardPartID}' AND DataStatus=1", engine)
    days = df.loc[0, '天数']
    return days


def get_mo_list():
    df = pd.read_sql("SELECT DOC_NO FROM MO_ITEM", engine)
    return list(df['DOC_NO'])


def match_if(item_code):
    """
    获取工艺与外形
    :param item_code:
    :return:
    """
    df = pd.read_sql(f"SELECT FEATURE_NAME,FEATURE_VALUE_DESC FROM ITEM_CODE_VALUE WHERE ITEM_CODE='{item_code}' "
                     f"AND (FEATURE_NAME='点粉工艺' or FEATURE_NAME='基板外形')", engine)
    size = None
    tech = None
    for i in df.itertuples():
        if getattr(i, 'FEATURE_NAME') == '点粉工艺':
            tech = getattr(i, 'FEATURE_VALUE_DESC')
        if getattr(i, 'FEATURE_NAME') == '基板外形':
            size = getattr(i, 'FEATURE_VALUE_DESC')
    return (size == '28×28' and tech == 'SD')


# 获得工单号匹配的冷热态信息
def get_cold_or_hot(MoID):
    df = pd.read_sql("SELECT * FROM ITEM_CODE_VALUE WHERE ITEM_CODE = (SELECT TOP 1  ITEM_CODE FROM MO_ITEM "
                     f"WHERE DOC_NO='{MoID}') ", engine)
    # 防呆,排除工单号错误的情况
    if len(df) != 0:
        is_cold = df[df['FEATURE_NAME'] == '冷态流明Max(lm)']['FEATURE_VALUE'].tolist()[0]
        if is_cold:
            return 25
        else:
            return 85
    else:
        return None


# 获取标准件的测试温度
def get_Temperture(StandardPartID):
    df = pd.read_sql(f"SELECT top 1 TestTemperature FROM StandardPartData "
                     f"WHERE StandardPartID='{StandardPartID}' and DataStatus=1", engine)
    # 防呆,排除标准件错误的情况
    if len(df) != 0:
        Temperture = df.iloc[0, 0]
        return Temperture
    else:
        return None


# 获取工单分光代码
def get_spec_code(item_code):
    df = pd.read_sql(f"SELECT FEATURE_VALUE FROM ITEM_CODE_VALUE WHERE ITEM_CODE='{item_code}' AND "
                     f"FEATURE_NAME='SPEC分光代码'", engine)
    spec_code = df.iloc[0][0]
    return 'SX' in spec_code


# 获取标准件类型:一次性,新增,过期
def get_standardPart_type(StandardPartID):
    df = pd.read_sql("SELECT StandardPart_Type,MoID FROM StandardPartData WHERE "
                     f"StandardPartID ='{StandardPartID}' AND DataStatus=1", engine)
    return df


# 获取小批量湿胶数据
def get_smallbatch_wet(MoID):
    smallbatch_wet_spec = pd.read_sql(f"SELECT top 1 * FROM Smallbatch_initial_Ratio WHERE moid='{MoID}';", engine)
    return smallbatch_wet_spec


# 获取量产湿胶数据
def get_mp_wet(MoID):
    smallbatch_wet_spec = pd.read_sql(f"SELECT top 1 * FROM Smallbatch_perfect WHERE moid = '{MoID}' "
                                      f"AND LotID = (SELECT MAX(LotID) FROM Smallbatch_perfect "
                                      f"WHERE moid = '{MoID}');", engine)
    return smallbatch_wet_spec


# 获取分光指标
def get_spec_select(item_code):
    spec_select = pd.read_sql(f"SELECT * FROM ITEM_CODE_VALUE as T1,SpecSelect as T2 WHERE T1.FEATURE_NAME='SPEC分光代码' "
                              f"AND T1.ITEM_CODE='{item_code}' AND T2.Category='Spec' AND T2.Status=1 "
                              f"AND T1.FEATURE_VALUE = T2.SpecCode collate Chinese_PRC_CI_AS;", engine)
    return spec_select


# 获取参数卡控
def get_spec_range(item_code):
    spec_range = pd.read_sql(f"SELECT * FROM SpecRange WHERE SpecCode = "
                             f"(SELECT FEATURE_VALUE FROM ITEM_CODE_VALUE WHERE FEATURE_NAME='SPEC分光代码' "
                             f"AND ITEM_CODE='{item_code}') collate Chinese_PRC_CI_AS AND Status=1", engine)
    return spec_range


# 获取分级数据
def get_bin_range(item_code):
    bin_range = pd.read_sql(f"SELECT * FROM SpecBinRange WHERE Status=1 and SpecCode = "
                            f"(SELECT FEATURE_VALUE FROM ITEM_CODE_VALUE WHERE FEATURE_NAME='SPEC分光代码' "
                            f"AND ITEM_CODE='{item_code}') collate Chinese_PRC_CI_AS", engine)
    return bin_range


# 获取不良Bin数据
def get_extra_bin(item_code):
    extra_bin = pd.read_sql(f"SELECT * FROM SpecExtraBin WHERE SpecCode = "
                            f"(SELECT FEATURE_VALUE FROM ITEM_CODE_VALUE WHERE FEATURE_NAME='SPEC分光代码' "
                            f"AND ITEM_CODE='{item_code}') collate Chinese_PRC_CI_AS", engine)
    return extra_bin


# 获取工单标准
def get_item_features(item_code, feature_name):
    item_features = pd.read_sql(f"SELECT TOP 1 * FROM ITEM_CODE_VALUE WHERE ITEM_CODE='{item_code}' "
                                f"AND FEATURE_NAME='{feature_name}'", engine)
    return item_features


# 获取对应的颜色标准(中心点)
def get_color_stand(item_code):
    color_stand = pd.read_sql(f"SELECT CCT_code_alias,CCT_STANDARD,CIEx,CIEy,a,b,θ FROM Color_Standard "
                              f"WHERE CCT_code_alias=(SELECT FEATURE_VALUE FROM ITEM_CODE_VALUE "
                              f"WHERE ITEM_CODE='{item_code}' and FEATURE_NAME='色温代码') "
                              f"collate Chinese_PRC_CI_AS", engine)
    return color_stand


# 成品转烤获取颜色标准(中心点)
def get_color_stand_2(moid):
    df = pd.read_sql(f"SELECT * FROM [dbo].[PE_Turn_to_roast] WHERE ITEM_NAME=(SELECT ITEM_NAME FROM MO_ITEM WHERE "
                     f"DOC_NO='{moid}') COLLATE Chinese_PRC_CS_AS",engine)
    return df


def get_V_A(item_code):
    va = pd.read_sql(f"select top 1 ITEM_SPECIFICATION FROM MO_ITEM WHERE ITEM_CODE='{item_code}'", engine)
    str1 = va.loc[0, 'ITEM_SPECIFICATION']
    list1 = str1.split(";")
    power = int(list1[0].split(":")[-1][:-1])
    A = int(list1[1].split(":")[-1][:-2])
    V = round(float(power / (A / 1000)), 2)
    return V, A


if __name__ == '__main__':
    print(get_mo_list())
    # print(get_smallbatch_wet('5101-20060361'))
    # print(get_mp_wet('5101-20060361'))

    # print((get_Spec_select('10105020270').loc[0][get_Spec_select('10105020270').loc[0]==1]))
    # print(get_item_features('10105020270').query("FEATURE_NAME=='CF2-调整系数代码'"))
    # print(get_spec_range('10105020439'))
